﻿using System;
using System.Data;
using System.Text;
using System.Data.OleDb;
using Maticsoft.DBUtility;//Please add references
namespace Maticsoft
{
    /// <summary>
    /// 类Products。
    /// </summary>
    [Serializable]
    public partial class Products
    {
        public Products()
        { }
        #region Model
        private int _id;
        private string _pname;
        private int? _ptype;
        private decimal? _pprice = 0;
        private int? _predu;
        private int? _pstatus = 1;
        private string _pmemo;
        private DateTime? _pdate = DateTime.Now;
        /// <summary>
        /// 
        /// </summary>
        public int ID
        {
            set { _id = value; }
            get { return _id; }
        }
        /// <summary>
        /// 
        /// </summary>
        public string PNAME
        {
            set { _pname = value; }
            get { return _pname; }
        }
        /// <summary>
        /// 
        /// </summary>
        public int? PTYPE
        {
            set { _ptype = value; }
            get { return _ptype; }
        }
        /// <summary>
        /// 
        /// </summary>
        public decimal? PPRICE
        {
            set { _pprice = value; }
            get { return _pprice; }
        }
        /// <summary>
        /// 
        /// </summary>
        public int? PREDU
        {
            set { _predu = value; }
            get { return _predu; }
        }
        /// <summary>
        /// 
        /// </summary>
        public int? PSTATUS
        {
            set { _pstatus = value; }
            get { return _pstatus; }
        }
        /// <summary>
        /// 
        /// </summary>
        public string PMEMO
        {
            set { _pmemo = value; }
            get { return _pmemo; }
        }
        /// <summary>
        /// 
        /// </summary>
        public DateTime? PDATE
        {
            set { _pdate = value; }
            get { return _pdate; }
        }
        #endregion Model


        #region  Method

        /// <summary>
        /// 是否存在该记录
        /// </summary>
        public bool Exists(String name, String id, String tid)
        {
            OleDbDataReader dr = null;
            try
            {
                StringBuilder strSql = new StringBuilder();
                strSql.Append("select count(PNAME) from [Products]");
                strSql.Append(" where PNAME=@PNAME ");
                if (id != "-1")
                {
                    strSql.Append(" and ID <> " + id + " and PTYPE <> " + tid);
                }
                OleDbParameter[] parameters = {
					new OleDbParameter("@PNAME", OleDbType.VarChar,255)};
                parameters[0].Value = name;
                dr = DbHelperOleDb.ExecuteReader(strSql.ToString(), parameters);
                return dr.HasRows;
            }
            finally
            {
                dr.Close();
                dr.Dispose();
            }
        }


        /// <summary>
        /// 增加一条数据
        /// </summary>
        public Boolean Add()
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into [Products] (");
            strSql.Append("PNAME,PTYPE,PPRICE,PREDU,PSTATUS,PMEMO,PDATE)");
            strSql.Append(" values (");
            strSql.Append("@PNAME,@PTYPE,@PPRICE,@PREDU,@PSTATUS,@PMEMO,@PDATE)");
            OleDbParameter[] parameters = {
					new OleDbParameter("@PNAME", OleDbType.VarChar,255),
					new OleDbParameter("@PTYPE", OleDbType.Integer,4),
					new OleDbParameter("@PPRICE", OleDbType.Decimal),
					new OleDbParameter("@PREDU", OleDbType.Integer,4),
					new OleDbParameter("@PSTATUS", OleDbType.Integer,4),
					new OleDbParameter("@PMEMO", OleDbType.VarChar,0),
					new OleDbParameter("@PDATE", OleDbType.Date)};
            parameters[0].Value = PNAME;
            parameters[1].Value = PTYPE;
            parameters[2].Value = PPRICE;
            parameters[3].Value = PREDU;
            parameters[4].Value = PSTATUS;
            parameters[5].Value = PMEMO;
            parameters[6].Value = PDATE;

            return DbHelperOleDb.ExecuteSql(strSql.ToString(), parameters) > 0;
        }
        /// <summary>
        /// 更新一条数据
        /// </summary>
        public bool Update()
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update [Products] set ");
            strSql.Append("PNAME=@PNAME,");
            strSql.Append("PTYPE=@PTYPE,");
            strSql.Append("PPRICE=@PPRICE,");
            strSql.Append("PSTATUS=@PSTATUS,");
            strSql.Append("PMEMO=@PMEMO,");
            strSql.Append("PDATE=@PDATE");
            strSql.Append(" where ID=@ID ");
            OleDbParameter[] parameters = {
					new OleDbParameter("@PNAME", OleDbType.VarChar,255),
					new OleDbParameter("@PTYPE", OleDbType.Integer,4),
					new OleDbParameter("@PPRICE", OleDbType.Decimal),
					new OleDbParameter("@PSTATUS", OleDbType.Integer,4),
					new OleDbParameter("@PMEMO", OleDbType.VarChar,0),
					new OleDbParameter("@PDATE", OleDbType.Date),
					new OleDbParameter("@ID", OleDbType.Integer,4)};
            parameters[0].Value = PNAME;
            parameters[1].Value = PTYPE;
            parameters[2].Value = PPRICE;
            parameters[3].Value = PSTATUS;
            parameters[4].Value = PMEMO;
            parameters[5].Value = PDATE;
            parameters[6].Value = ID;

            int rows = DbHelperOleDb.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 删除一条数据
        /// </summary>
        public bool Delete(int ID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from [Products] ");
            strSql.Append(" where ID=@ID ");
            OleDbParameter[] parameters = {
					new OleDbParameter("@ID", OleDbType.Integer,4)};
            parameters[0].Value = ID;

            int rows = DbHelperOleDb.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }


        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public void GetModel(int ID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ID,PNAME,PTYPE,PPRICE,PREDU,PSTATUS,PMEMO,PDATE ");
            strSql.Append(" FROM [Products] ");
            strSql.Append(" where ID=@ID ");
            OleDbParameter[] parameters = {
					new OleDbParameter("@ID", OleDbType.Integer,4)};
            parameters[0].Value = ID;

            DataSet ds = DbHelperOleDb.Query(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                if (ds.Tables[0].Rows[0]["ID"] != null && ds.Tables[0].Rows[0]["ID"].ToString() != "")
                {
                    this.ID = int.Parse(ds.Tables[0].Rows[0]["ID"].ToString());
                }
                if (ds.Tables[0].Rows[0]["PNAME"] != null && ds.Tables[0].Rows[0]["PNAME"].ToString() != "")
                {
                    this.PNAME = ds.Tables[0].Rows[0]["PNAME"].ToString();
                }
                if (ds.Tables[0].Rows[0]["PTYPE"] != null && ds.Tables[0].Rows[0]["PTYPE"].ToString() != "")
                {
                    this.PTYPE = int.Parse(ds.Tables[0].Rows[0]["PTYPE"].ToString());
                }
                if (ds.Tables[0].Rows[0]["PPRICE"] != null && ds.Tables[0].Rows[0]["PPRICE"].ToString() != "")
                {
                    this.PPRICE = decimal.Parse(ds.Tables[0].Rows[0]["PPRICE"].ToString());
                }
                if (ds.Tables[0].Rows[0]["PREDU"] != null && ds.Tables[0].Rows[0]["PREDU"].ToString() != "")
                {
                    this.PREDU = int.Parse(ds.Tables[0].Rows[0]["PREDU"].ToString());
                }
                if (ds.Tables[0].Rows[0]["PSTATUS"] != null && ds.Tables[0].Rows[0]["PSTATUS"].ToString() != "")
                {
                    this.PSTATUS = int.Parse(ds.Tables[0].Rows[0]["PSTATUS"].ToString());
                }
                if (ds.Tables[0].Rows[0]["PMEMO"] != null && ds.Tables[0].Rows[0]["PMEMO"].ToString() != "")
                {
                    this.PMEMO = ds.Tables[0].Rows[0]["PMEMO"].ToString();
                }
                if (ds.Tables[0].Rows[0]["PDATE"] != null && ds.Tables[0].Rows[0]["PDATE"].ToString() != "")
                {
                    this.PDATE = DateTime.Parse(ds.Tables[0].Rows[0]["PDATE"].ToString());
                }
            }
        }

        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataSet GetList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select p.ID,p.PPRICE,p.PREDU,p.PDATE,p.PNAME,c.TNAME as PTYPE ");
            strSql.Append(" FROM [Products] as p left join Categorys as c on p.PTYPE=c.ID ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere + " order by PDATE DESC");
            }
            return DbHelperOleDb.Query(strSql.ToString());
        }
        public String GetList(int page, int rows, Maticsoft.Query query)
        {
            StringBuilder str = new StringBuilder();
            str.Append(" PDATE >= #" + query.from + "# and PDATE <= #" + query.to + "#");
            if (query.name != "")
            {
                str.Append(" and PNAME %" + query.name + "%");
            }
            if (query.type != "")
            {
                str.Append(" and PTYPE in (" + query.type + ") ");
            }
            DataSet ds = GetList(str.ToString());
            if (ds != null && ds.Tables.Count != 0)
            {
                DataTable dt = ds.Tables[0];
                if (dt != null && dt.Rows.Count != 0)
                {
                    DataTable newDt = dt.Clone();
                    int rowCount = dt.Rows.Count - dt.Rows.Count % rows == rows * (page - 1) ? dt.Rows.Count : page * rows;
                    for (int i = rows * (page - 1); i < rowCount; i++)
                    {
                        Object[] dr = dt.Rows[i].ItemArray;
                        newDt.Rows.Add(dr);
                    }
                    return "{\"total\":" + dt.Rows.Count + ",\"rows\":" + DataTableExt.DataTableToJson(newDt).ToString() + "}";
                }
                return "{\"total\":0,\"rows\":[]}";
            }
            return "{\"total\":0,\"rows\":[]}";
        }
        #endregion  Method
    }
}

